package co.com.colcomercio.interfaceRecaudos.sufi.dao.impl;

import co.com.colcomercio.conf.dao.impl.DAOSuper;
import co.com.colcomercio.interfaceRecaudos.sufi.dao.RecaudosSufiDAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

public class RecaudosSufiDAOImpl extends DAOSuper implements RecaudosSufiDAO {

	
	public void obtieneConsulta(String fecha, File archivo) {
		
		FileOutputStream fos = null;
		Connection conn = null;
		
		String query;
		query = "select '9974000000' || t.pos || lpad(t.ticketnumber,12, '0') || to_char(t.opendate, 'yyyymmddhh24mmss') || " +
				"'00000000000000000408002' || lpad(t.total,15, '0')  || substr(rpad(t.cashier,10,'0'),0,8)  || " +
				"( select value from ticketitemproperties where pos = t.pos and localid = t.localid and ticketnumber = t.ticketnumber and name = 'account')" +
				"|| '01' || t.localid as registro from tickets t " +
				"join ticketitems ti on (ti.pos = t.pos  and ti.localid = t.localid and ti.ticketnumber = t.ticketnumber) " +
				"where ti.description = 'SUFINANCIAMIENTO S.A.' and to_char(t.opendate, 'YYYYMMDD') >='" + fecha +
				"' and to_char(t.opendate, 'YYYYMMDD') <= '" + fecha + "' and t.documenttype = 'service'";
		//System.out.println(query);
		
		try {
			conn = getConnection();
			
			Statement stm = conn.createStatement();
			ResultSet rset = stm.executeQuery(query);
			fos = new FileOutputStream(archivo);
			String linea = "";
			while(rset.next()){
				linea = rset.getString(1);
				//System.out.println(rset.getString(1));
				linea+='\n';
				fos.write(linea.getBytes());
			}
			fos.close();
			closeResultSet(rset);
			stm.close();
		} catch (IOException ex) {
			Logger.getLogger(RecaudosSufiDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
		} catch (SQLException ex) {
			Logger.getLogger(RecaudosSufiDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
		   
		} 
		finally
		{
			closeConnection(conn);
		}
	}

}
